#!/usr/bin/env python
#! -*-coding:utf-8-*-
import getopt, sys

from app.core.base_class import MyBase
from app.core.model.dw_meta_manager import DwmetaManager
from app.core.etl_db_executor import EtlMysqlExcutor
from app.core.model import g_dwsdataManager
from app.core.model.dw_meta_model import TbFile
from app.core.model.dw_meta_model import TbFldLenNew

create_template = u'''
CREATE TABLE {table_name} (
    {sql_field_snippet},
{sql_keys_snippet}
) COMMENT='{table_comment}';
'''
create_primary_keys = u'''PRIMARY KEY({primary_keys})'''
create_field_tempalte = u'''{field} {type} COMMENT '{comment}' '''

class SqlCommand(MyBase):
    
    def __init__(self):
        MyBase.__init__(self)
        self.debug('Creator init start...')
        self.dw_manager = DwmetaManager()
        self.debug('Creator init complement.')

    def get_tb_name_list(self):
        session = self.dw_manager.get_session()
        tb_files = session.query(TbFile).order_by(TbFile.tab_name)
        tb_name_list = [t.tab_name for t in tb_files]
        session.close()
        return tb_name_list

    def get_tbFile(self, tb_name):
        session = self.dw_manager.get_session()
        tb_info = [t for t in session.query(TbFile).filter_by(tab_name = tb_name)]
        return tb_info

    def get_tb_field_list(self, tb_name):
        self.debug('get_tb_field_list: %s', tb_name)
        session = self.dw_manager.get_session()
        tb_fields = [f for f in session.query(TbFldLenNew).filter_by(tab_name = tb_name)]
        session.close()
        return tb_fields

    def get_create_sql(self, tb_name, tb_date=None):
        self.debug('get_create_sql begin')
        tb_info = self.get_tbFile(tb_name)
        tb_fields = self.get_tb_field_list(tb_name)
        tab_name = tb_info[0].tab_name
        if tb_date:
            tb_name = "%s_%s" % (tb_name, tb_date)
        self.debug(tb_name)
        tab_desc = tb_info[0].source_tab_desc
        self.debug('fields count: %s', len(tb_fields))

        #fields snippet
        sql_fields = [
            create_field_tempalte.format(field=field.col_name, comment=field.source_col_desc, type=field.col_type) 
            for field in tb_fields
        ]
        sql_field_snippet = ',\n    '.join(sql_fields)

        #keys snippet
        sql_keys = [
            field.col_name
            for field in tb_fields if field.pk_flag == '1'
        ]
        sql_keys_snippet = create_primary_keys.format(primary_keys=','.join(sql_keys))
        
        #create sql
        create_sql = create_template.format(table_name=tb_name,sql_field_snippet=sql_field_snippet,\
        sql_keys_snippet=sql_keys_snippet, table_comment=tab_desc)

        self.debug('get_create_sql end')
        return create_sql

    def execute_create_table(self, tb_name):
        sql_create = self.get_create_sql(tb_name)
        self.debug(sql_create)
        etl_excutor = EtlMysqlExcutor()
        etl_excutor.set_connection(g_dwsdataManager.get_connection())
        etl_excutor.execute(sql_create)
        etl_excutor.close()
    pass


def usage():
    print 'uage'

def main():
    command = SqlCommand()
    try:
        opts, args = getopt.getopt(sys.argv[1:], "lc:e:", ["list", "create=", "execute="])
    except getopt.GetoptError as err:
        print str(err) # will print something like "option -a not recognized"
        usage()
        sys.exit(2)
    for o, a in opts:
        if o in ("-l", "--list"):
            tb_names = command.get_tb_name_list()
            print '***table name***'
            print "\n".join(tb_names)
            print '****************'
            sys.exit()
        elif o in ("-c", "--create="):
            tb_name = a
            create_sql = command.get_create_sql(tb_name)
            print '***table ddl***'
            print create_sql
            print '***************'
        elif o in ("-e", "--execute="):
            tb_name = a
            command.execute_create_table(tb_name)
        else:
            assert False, "unhandled option"

if __name__ == '__main__':
    main()
'''
    command = SqlCommand()
    #print command.get_tb_name_list()
    tb_name =  'II_LMRiskApp'
    #command.get_create_sql(tb_name, '2015_11_12')
    command.get_create_sql(tb_name)
    args = getopt.getopt(sys.argv[1:], 'a:b:')
    #print args
'''
